How to: Sum a calculated control in a report.
Solution:
In the Form Design view, click 'Text Box' from the Toolbox. Type the desired expression using the Sum() function.
1) If the Database window is not active, activate the Database window.
2) Click the 'Reports' tab in the Database window.
Reports tab
3) Select the desired report from the list box. (The selected report is highlighted.)
4) Click 'Design'. (The selected report appears in the Design view, and the floating Toolbox appears.)
5) Make sure the 'Control Wizard' button located in the 'Toolbox' is depressed.
Control Wizard and Text Box
6) Add a text box to calculate an expression containing the desired fields.
a) Single-click on the 'Text Box' button from Toolbox.
b) Click the desired location for the text box in the detail section of the report. (Both the label and the text box appear on the report.)
Label box and text box.
c) Set properties for the control:
1] Set the name:
a] Single-click on the text box. (Handles appear around the box.)
Handles around the text box
b] Select the 'View' menu and 'Properties'. (The property sheet appears.)
c] Click the 'Other' tab.
d] Single-click in the 'Name' text box.
e] Type the desired character string in the 'Name' text box.
2] Set the ControlSource:
a] Click the 'Data' tab.
Properties button and property sheet
b] Click in the 'Control Source' property box. (A drop-down list appears.)
c] Select the field(s) to be used in the expression.
Data Tab and drop-down list
NOTE: Separate each field with an operator.
d] Build the expression using the desired operators in the 'Control Source' property box.
NOTE: Integers can be used as part of the expression.
EXAMPLE: =<[Unitprice]>*5
(where <[Unitprice]> is a field from the selected table or query that is to be multiplied by 5).
Control Source property box
3] To close the property sheet, click the 'X' button in the upper-right hand corner of the property sheet.
Click the X button to close property sheet
NOTE: The properties will be saved only when the design view is saved.
7) Edit the label box:
a) Select the label box. (Handles appear around the box.)
b) Select the 'View' menu and select 'Properties'. (The property sheet appears.)
c) Select the 'Format' tab.
d) Type the name of the label box in the 'Caption' property box.
Editing the label box
8) Add a text box control to the group footer section and/or the report footer section that sums the total for that section:
a) Click the 'Text box' icon on the toolbar.
b) Single-click in the desired location in the group footer or report footer section. (Both a label and a text box appear on the report.)
Label box and text box.
c) To set properties for the control, repeat Step 6) in its entirety.
NOTE: Build the expression starting with the Sum() function and using the desired operators in the 'Control Source' property box.
EXAMPLE: <=Sum([Unitprice] * 5)>
9) To edit the label box, repeat step 7) above.
